﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Entity;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Wei.DB;
using Wei.Model;

namespace Wei.BLL
{
    public class UserBLL
    {

        public List<User_Model> get_user_list()
        {
            try
            {
                using (WeiEntities db = new WeiEntities())
                {
                    var sql = from a in db.wei_users
                              select new User_Model
                              {
                                  u_name = a.u_name,
                                  u_create_date = a.u_create_date,
                                  u_email = a.u_email,
                                  u_phone = a.u_phone
                              };
                    return sql.ToList();
                }
            }
            catch (Exception)
            {

                throw;
            }
        }


        public async Task<List<User_Model>> get_user_list_by_async()
        {
            using (WeiEntities db = new WeiEntities())
            {
                var sql = from a in db.wei_users
                          select new User_Model
                          {
                              u_name = a.u_name,
                              u_create_date = a.u_create_date,
                              u_email = a.u_email,
                              u_phone = a.u_phone
                          };
                return await sql.ToListAsync();
            }
        }

        public string Edit_User(User_Model user,string user_img, bool not)
        {
            using (WeiEntities db = new WeiEntities())
            {
                var sql = db.wei_users.Where(u => u.u_id == user.u_id).FirstOrDefault();
                if (sql == null)
                {
                    return "用户不存在！";
                }
                if (not)
                {
                    var sql1 = db.wei_users.Where(u => u.u_phone == user.u_phone).FirstOrDefault();
                    if (sql1 != null)
                    {
                        return "手机号已经存在！";
                    }
                }
                SqlParameter[] para = new SqlParameter[]
                {
                    new SqlParameter("@u_id",user.u_id),
                    new SqlParameter("@u_name",user.u_name),
                    new SqlParameter("@u_phone",user.u_phone),
                    new SqlParameter("@u_sex",user.u_sex),
                    new SqlParameter("@u_age",user.u_age),
                    new SqlParameter("@u_style_sign",user.u_style_sign),
                    new SqlParameter("@u_user_img",user_img)
                };
                return db.Database.ExecuteSqlCommand("update wei_users set u_sex=@u_sex,u_age=@u_age,u_name=@u_name,u_phone=@u_phone,u_style_sign=@u_style_sign,u_user_img =@u_user_img where u_id=@u_id", para).ToString();
            }
        }

        public List<Friend_Link_Model> Get_Friend_Link_List()
        {
            using (WeiEntities db = new WeiEntities())
            {
                var sql = from a in db.wei_friend_link
                          select new Friend_Link_Model()
                          {
                              fl_icon = a.fl_icon,
                              fl_id = a.fl_id,
                              fl_title = a.fl_title,
                              fl_url = a.fl_url
                          };
                return sql.ToList();
            }
        }

        public System_Model Get_Index_Img()
        {
            using (WeiEntities db = new WeiEntities())
            {
                var sql = from a in db.wei_system
                          select new System_Model
                          {
                              s_img = a.s_img,
                              sy_show_comment = a.sy_show_comment==true ? "是":"否",
                              sy_show_friend_link = a.sy_show_friend_link == true ? "是":"否",
                          };
                return sql.FirstOrDefault();
            }
        }

        public User_Model Login(string email, string pwd,int type)
        {
            try
            {
                using (WeiEntities db = new WeiEntities())
                {
                    string get_pwd = Tool.Tool.set_pwd(pwd);
                    var sql = from a in db.wei_users
                              where a.u_email == email & a.u_password == get_pwd & a.u_type == type
                              select new User_Model
                              {
                                  u_name = a.u_name,
                                  u_email = a.u_email,
                                  u_phone = a.u_phone,
                                  u_type = a.u_type,
                                  u_id = a.u_id,
                                  u_sex = a.u_sex,
                                  u_age= a.u_age,
                                    u_style_sign = a.u_style_sign,
                                    u_img = a.u_user_img
                              };
                    User_Model user = sql.FirstOrDefault();
                    if (user != null)
                    {
                        return user;
                    }
                    return null;
                }
            }
            catch (Exception)
            {

                throw;
            }
        }

        public List<Friend_Link_Model> Friend_Link_List(int pageIndex, int pageSize, string fl_title, out int count)
        {
            using (WeiEntities db = new WeiEntities())
            {
                var sql = from a in db.wei_friend_link
                          select new Friend_Link_Model
                          {
                              fl_id = a.fl_id,
                              fl_icon = a.fl_icon,
                              fl_title = a.fl_title,
                              fl_url = a.fl_url
                          };
                if (fl_title != "")
                {
                    sql = sql.Where(c => c.fl_title.Contains(fl_title));
                }
                count = sql.Count();
                return sql.OrderBy(t => t.fl_id).Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();
            }
        }

        public List<System_Model> Get_System_Data(int pageIndex, int pageSize, string fl_title, out int count)
        {
            using (WeiEntities db = new WeiEntities())
            {
                var sql = from a in db.wei_system
                          select new System_Model
                          {
                              sy_id = a.sy_id,
                              sy_show_comment = a.sy_show_comment == true ? "是" : "否",
                              sy_show_friend_link = a.sy_show_friend_link == true ? "是" : "否"
                          };
                if (fl_title != "")
                {
                    
                }
                count = sql.Count();
                return sql.OrderBy(t => t.sy_id).Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();
            }
        }


        public string Chong_Zhi(int id,ref string pwd)
        {
            using (WeiEntities db = new WeiEntities())
            {
                var sql = db.wei_users.Where(u => u.u_id == id).FirstOrDefault();
                if (sql == null)
                {
                    return "用户不存在！";
                }
                pwd = new Random().Next(100000,999999).ToString();
                SqlParameter[] para = new SqlParameter[]
                {
                    new SqlParameter("@id",id),
                    new SqlParameter("@pwd",Tool.Tool.set_pwd(pwd))
                };
                return db.Database.ExecuteSqlCommand("update wei_users set u_password=@pwd where u_id=@id", para).ToString();
            }
        }

        public string Del_User(int u_id)
        {
            using (WeiEntities db = new WeiEntities())
            {
                var sql = db.wei_users.Where(u => u.u_id == u_id).FirstOrDefault();
                if (sql == null)
                {
                    return "用户不存在";
                }
                db.wei_users.Remove(sql);
                
                return db.SaveChanges().ToString();
            }
        }

        public string Edit_User(int u_id, string u_name, string u_phone, string u_email)
        {
            using (WeiEntities db = new WeiEntities())
            {
                var sql = db.wei_users.Where(u => u.u_id == u_id).FirstOrDefault();
                if (sql == null)
                {
                    return "用户不存在";
                }
                SqlParameter[] para = new SqlParameter[] {
                    new SqlParameter("@u_id",u_id),
                    new SqlParameter("@u_name",u_name),
                    new SqlParameter("@u_phone",u_phone),
                    new SqlParameter("@u_email",u_email)
                 };
                string str = "update wei_users set u_name=@u_name,u_phone=@u_phone,u_email=@u_email where u_id=@u_id";
               return db.Database.ExecuteSqlCommand(str, para).ToString();
            }
        }

        public List<User_Model> GetUserList(int pageIndex, int pageSize, string nickName, out int count)
        {
            using (WeiEntities db = new WeiEntities())
            {
                var sql = from a in db.wei_users
                          select new User_Model
                          {
                            u_age = a.u_age,
                            u_create_date = a.u_create_date,
                            u_email = a.u_email,
                            u_id = a.u_id,
                            u_img = a.u_user_img,
                            u_name = a.u_name,
                            u_phone = a.u_phone,
                            u_sex = a.u_sex
                          };
                if (nickName != "")
                {
                    sql = sql.Where(c => c.u_name.Contains(nickName));
                }
                count = sql.Count();
                return sql.OrderByDescending(t => t.u_create_date).Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();
            }
        }
        

        public string Delete_Friend_Link(int id)
        {
            using (WeiEntities db = new WeiEntities())
            {
                var sql = db.wei_friend_link.Where(f => f.fl_id == id).FirstOrDefault();
                if (sql == null)
                {
                    return "记录不存在";
                }
                SqlParameter[] para = new SqlParameter[]
                 {
                    new SqlParameter("@id",id)
                 };
                return db.Database.ExecuteSqlCommand("delete wei_friend_link where fl_id=@id", para).ToString();
            }
        }

        public string Update_Friend_Link(int id, string title, string url)
        {
            using (WeiEntities db = new WeiEntities())
            {
                var sql = db.wei_friend_link.Where(f => f.fl_id == id).FirstOrDefault();
                if (sql == null)
                {
                    return "记录不存在";
                }
                SqlParameter[] para = new SqlParameter[]
                 {
                    new SqlParameter("@fl_title",title),
                    new SqlParameter("@fl_url",url),
                    new SqlParameter("@id",id)
                 };
                return db.Database.ExecuteSqlCommand("update wei_friend_link set fl_url = @fl_url,fl_title = @fl_title where fl_id=@id", para).ToString();
            }
        }
        public string Update_System_Data(string comment, string friend_link)
        {
            using (WeiEntities db = new WeiEntities())
            {
                var sql = db.wei_system.FirstOrDefault();
                if (sql == null)
                {
                    return "记录不存在";
                }
                SqlParameter[] para = new SqlParameter[]
                 {
                    new SqlParameter("@sy_show_comment",comment=="是" ? 1 : 0),
                    new SqlParameter("@sy_show_friend_link",friend_link=="是" ? 1 : 0)
                 };
                return db.Database.ExecuteSqlCommand("update wei_system set sy_show_comment = @sy_show_comment,sy_show_friend_link = @sy_show_friend_link", para).ToString();
            }
        }

        public string Add_Friend_Link(string fl_title, string fl_url)
        {
            using (WeiEntities db = new WeiEntities())
            {
                var sql = db.wei_friend_link.Where(f => f.fl_title == fl_title).FirstOrDefault();
                if (sql != null)
                {
                    return "标题已存在";
                }
                SqlParameter[] para = new SqlParameter[]
                 {
                    new SqlParameter("@fl_title",fl_title),
                    new SqlParameter("@fl_url",fl_url)
                 };
                return db.Database.ExecuteSqlCommand("insert into wei_friend_link(fl_url,fl_title) values(@fl_url,@fl_title)", para).ToString();
            }
        }

        public string Update_Index_Img(string svaePath)
        {
            using (WeiEntities db = new WeiEntities())
            {
                SqlParameter para = new SqlParameter("@s_img", svaePath);
                string sql = "update wei_system set s_img = @s_img";
                return db.Database.ExecuteSqlCommand(sql, para).ToString();
            }
        }

        /// <summary>
        /// 新增用户
        /// </summary>
        /// <param name="email"></param>
        /// <param name="password"></param>
        /// <returns></returns>
        public string Add_User(string email, string password,string name)
        {
            using (WeiEntities db = new WeiEntities())
            {
                var model = db.wei_users.Where(u => u.u_email == email).FirstOrDefault();
                if (model != null)
                {
                    return "该账户已经存在";
                }
                string pwd = Tool.Tool.set_pwd(password);
                SqlParameter[] para = new SqlParameter[]
                {
                    new SqlParameter("@u_email",email),
                    new SqlParameter("@u_pwd",pwd),
                    new SqlParameter("@u_name",name)
                };
                string sql = "insert into wei_users(u_email,u_password,u_create_date,u_last_update_date,u_type,u_name,u_user_img) values(@u_email,@u_pwd,getdate(),getdate(),2,@u_name,'/images/default2.jpg')";
                return db.Database.ExecuteSqlCommand(sql, para).ToString();
            }
        }
    }
}
